CREATE OR REPLACE package oravc_trigger as

/*
<main_description>
  creator: Andrey Tsyganov
     date: January, 2014
  contact: atsygan@gmail.com, A.Tsyganov@rug.nl
  (c) 2011, all rights recerved

  Developer of this package is NOT responsible in case of any change of this coe by its users!
  and also NOT responsible for any bugs in the functionality of the package, however support CAN be provided

  This package is providing possibility to create OraVC triggers
  for the other schemas, thus all DDL event will be logged
</main_description>

*/

/*
<comments>
  <function>
    getTriggerTemplate
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returns CLOB with the TRIGGER template text
  </description>

  <parameters>
    <IN>
      [p_username]    [username to which trigger should be created]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [26-10-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getTriggerTemplate           RETURN CLOB;

/*
<comments>
  <function>
    oravc_getVCTriggerTextForUser
  </function>

  <return>
    CLOB
  </return>

  <description>
    Procedure creates the source code for thr Version Controll SCHEMA Trigger
    for the given user but only for the users registered in
    user2monitor table and for objects registered in types2monitor table. (see G_TRIGGET_TEMPLATE variable in the package body)
    The trigger will fill ORAVC_OBJ_TRGCHANGES table. This data will be processed by the sheduler job
    co register source changes of the objects.
  </description>

  <parameters>
    <IN>
      [p_username]    [username to which trigger should be created]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [29-01-2014] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_getVCTriggerTextForUser(
            p_username                    VARCHAR2
          ) RETURN CLOB;
          
/*
<comments>
  <function>
    oravc_getVCTriggerNameForUser
  </function>

  <return>
    VARCHAR2
  </return>

  <description>
    Function returnes the Version Controll trigger generated for the given user
  </description>

  <parameters>
    <IN>
      [p_username]    [username to which trigger should be created]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [29-01-2014] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_getVCTriggerNameForUser(
            p_username                    VARCHAR2
          ) RETURN VARCHAR2;

/*
<comments>
  <function>
    oravc_getVCTrgDropTxtForUser
  </function>

  <return>
    VARCHAR2
  </return>

  <description>
    Function returnes the Version Controll trigger drop command text for the given user
  </description>

  <parameters>
    <IN>
      [p_username]    [username to which trigger should be created]   [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [29-01-2014] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION oravc_getVCTrgDropTxtForUser(
            p_username                    VARCHAR2
          ) RETURN VARCHAR2;

end oravc_trigger;
/


CREATE OR REPLACE package body oravc_trigger as

C_PACKAGE_NAME                      CONSTANT VARCHAR2(255) := 'ORAVC_TRIGGER';

C_TRIGGER_TEMPLATE_TAG0             CONSTANT VARCHAR2(255) := 'BEFORE';
C_TRIGGER_TEMPLATE_TAG1             CONSTANT VARCHAR2(255) := '<###TRIGGER_NAME###>';
C_TRIGGER_TEMPLATE_TAG2             CONSTANT VARCHAR2(255) := '<###SCHEMA_NAME###>';
C_TRIGGER_TEMPLATE_TAG3             CONSTANT VARCHAR2(255) := '<###ORAVCUSER_NAME###>';
C_TRIGGER_NAME                      CONSTANT VARCHAR2(255) := '_oravctrg';

G_TRIGGET_TEMPLATE CLOB:=
'
create or replace TRIGGER ' || C_TRIGGER_TEMPLATE_TAG1 || ' '
|| C_TRIGGER_TEMPLATE_TAG0 || ' DDL ON ' || C_TRIGGER_TEMPLATE_TAG2 || 'SCHEMA

DECLARE

l_run_sting VARCHAR2(4000) :=
''
DECLARE
  l_count        NUMBER := 100;
  l_triggername  VARCHAR2(255) := ''''' || C_TRIGGER_TEMPLATE_TAG1 || ''''';
  l_errmsg       VARCHAR2(255) := '''''''';
  
  l_trgpar_objowner   VARCHAR2(255) := :ora_dict_obj_owner;
  l_trgpar_objname    VARCHAR2(255) := :ora_dict_obj_name;
  l_trgpar_objtype    VARCHAR2(255) := :ora_dict_obj_type;
  l_trgpar_sysevent   VARCHAR2(255) := :ora_sysevent;

PROCEDURE log_error IS
BEGIN
  INSERT INTO ' || C_TRIGGER_TEMPLATE_TAG3 || 'oravc_log(
                vcl_id
               ,vcl_datetime
               ,vcl_message_type
               ,vcl_message_text
               ,vcl_message_place
               )
         VALUES(' || C_TRIGGER_TEMPLATE_TAG3 || 'sq_obj_vc_id.NEXTVAL
               ,SYSDATE
               ,''''WARNING''''
               ,l_errmsg
               ,l_triggername
               );
END;

PROCEDURE save_event IS
BEGIN
  INSERT INTO ' || C_TRIGGER_TEMPLATE_TAG3 || 'oravc_log(
                vcl_id
               ,vcl_datetime
               ,vcl_message_type
               ,vcl_message_text
               ,vcl_message_place
               )
        VALUES( ' || C_TRIGGER_TEMPLATE_TAG3 || 'sq_obj_vc_id.NEXTVAL
               ,SYSDATE
               ,''''EVENT''''
               ,''''NEW event was detected. '''' || l_trgpar_sysevent || '''' for '''' || l_trgpar_objtype || '''' object '''' || l_trgpar_objowner || ''''.'''' || l_trgpar_objname || chr(10)
               ,l_triggername
              );

  INSERT INTO  ' || C_TRIGGER_TEMPLATE_TAG3 || 'oravc_obj_trgchanges(
               oot_record_id
              ,oot_event
              ,oot_eventtime
              ,oot_objowner
              ,oot_objname
              ,oot_objtype
              ,oot_objversioninfo
              ,oot_objsource
              )
       VALUES ( ' || C_TRIGGER_TEMPLATE_TAG3 || 'sq_obj_vc_id.NEXTVAL
              ,ora_sysevent
              ,SYSDATE
              ,l_trgpar_objowner
              ,l_trgpar_objname
              ,l_trgpar_objtype
              ,''''OS_USER=''''   || sys_context(''''USERENV'''',''''OS_USER'''')       || '''';'''' ||
               ''''ORACLE_USER='''' || sys_context(''''USERENV'''',''''CURRENT_USER'''')  || '''';'''' ||
               ''''HOST=''''        || sys_context(''''USERENV'''',''''HOST'''')          || '''';'''' ||
               ''''TERMINAL=''''    || sys_context(''''USERENV'''',''''TERMINAL'''')      || '''';''''
              ,DBMS_METADATA.GET_DDL(
                  decode(l_trgpar_objtype, ''''JAVA SOURCE''''          ,  ''''JAVA_SOURCE''''
                                          , ''''MATERIALIZED VIEW''''    , ''''MATERIALIZED_VIEW''''
                                          , ''''MATERIALIZED VIEW LOG'''', ''''MATERIALIZED_VIEW_LOG''''
                                          , ''''PACKAGE''''              , ''''PACKAGE_SPEC''''
                                          , ''''PACKAGE BODY''''         , ''''PACKAGE_BODY''''
                        ,l_trgpar_objtype
                        )
                ,l_trgpar_objname
                ,l_trgpar_objowner
              )
            );
END;

BEGIN
  SELECT count(*)
    INTO l_count
    FROM dual
   WHERE ora_dict_obj_name NOT LIKE ''''SYS_PLSQL%'''';

  IF l_count > 0 THEN
    SELECT count(*)
      INTO l_count
      FROM ' || C_TRIGGER_TEMPLATE_TAG3 || 'oravc_usr2monitor
     WHERE vc_ora_user = l_trgpar_objowner;
  END IF;

  IF l_count > 0 THEN
    l_count := 0;
    SELECT count(*)
      INTO l_count
      FROM  ' || C_TRIGGER_TEMPLATE_TAG3 || 'oravc_event2monitor
     WHERE vc_ora_event = l_trgpar_sysevent;

    IF l_count > 0 THEN
      SELECT count(*)
        INTO l_count
        FROM  ' || C_TRIGGER_TEMPLATE_TAG3 || 'oravc_usr2monitor
       WHERE vc_ora_user = l_trgpar_objowner;
    END IF;

    IF l_count > 0 THEN
      SELECT count(*)
        INTO l_count
        FROM  ' || C_TRIGGER_TEMPLATE_TAG3 || 'oravc_objtype2monitor
       WHERE vc_ora_objtype = l_trgpar_objtype;
    END IF;

  END IF;

  IF l_count > 0 THEN
    save_event;
  END IF;


EXCEPTION WHEN OTHERS THEN
  l_errmsg := SUBSTR(SQLERRM, 1, 4000);
  log_error;
END;
'';




BEGIN
  EXECUTE IMMEDIATE l_run_sting
  USING ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, ora_sysevent;


EXCEPTION WHEN OTHERS THEN
  -- this is forced to be done to a void trigger influence on the general functionality
  -- of the other applications
  NULL;

END;
';

FUNCTION getTriggerTemplate           RETURN CLOB      IS BEGIN RETURN G_TRIGGET_TEMPLATE;      END;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION oravc_getVCTriggerTextForUser(
            p_username                    VARCHAR2
          ) RETURN CLOB IS

  l_current_schema                        VARCHAR2(255) := '';
  l_function_name                         VARCHAR2(255) := 'oravc_compileVCTriggerForUser';
  l_trg_name                              VARCHAR2(30) := '';
  l_trigger                               CLOB;
  l_userscount                            NUMBER;
BEGIN
  SELECT sys_context('USERENV', 'CURRENT_SCHEMA')
    INTO l_current_schema
    FROM dual;

  SELECT count(*)
    INTO l_userscount
    FROM oravc_usr2monitor
   WHERE vc_ora_user = UPPER(p_username);

  l_trigger := 'User_is_not_registered_with_OraVC_engine';
  IF l_userscount > 0 THEN
    l_trg_name := SUBSTR(p_username || C_TRIGGER_NAME, 1,30);

    l_trigger := getTriggerTemplate();
    l_trigger := REPLACE(l_trigger, C_TRIGGER_TEMPLATE_TAG1, l_trg_name);
    l_trigger := REPLACE(l_trigger, C_TRIGGER_TEMPLATE_TAG2, p_username || '.');
    l_trigger := REPLACE(l_trigger, C_TRIGGER_TEMPLATE_TAG3, l_current_schema || '.');

    oravc_engine.add2log(
         p_message_type    => oravc_engine.get_C_EVENT
        ,p_message_text    => 'OraVC trigger text for the schema: ' || p_username || ' was generate'
        ,p_message_place   => C_PACKAGE_NAME || '.' || l_function_name
      );
  ELSE
    oravc_engine.add2log(
         p_message_type    => oravc_engine.get_C_ERROR
        ,p_message_text    => 'Can''t generate trigger text for the schema ' || p_username || ' - user is not registered with OraVC engine. Add users to oravc_usr2monitor table using: execute oravc_engine.oravc_add_user2monitor(<username>);'
        ,p_message_place   => C_PACKAGE_NAME || '.' || l_function_name
      );

  END IF;

  RETURN l_trigger;
END oravc_getVCTriggerTextForUser;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION oravc_getVCTriggerNameForUser(
            p_username                    VARCHAR2
          )  RETURN VARCHAR2 IS
BEGIN
  RETURN SUBSTR(p_username || C_TRIGGER_NAME, 1,30);
END oravc_getVCTriggerNameForUser;

/*
<comments>
  in package specification

</comments>
*/
FUNCTION oravc_getVCTrgDropTxtForUser(
            p_username                    VARCHAR2
          )  RETURN VARCHAR2 IS
BEGIN
  RETURN 'DROP TRIGGER ' || p_username || ' ' || SUBSTR(p_username || C_TRIGGER_NAME, 1,30);
END oravc_getVCTrgDropTxtForUser;

end oravc_trigger;
/
